<html>
  <head>
    <title>BIDS</title>

    <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
    <meta charset="UTF-8">

    <!-- ADD ALL APIS AND INCLUDED SCRIPTS -->

    <link ref="/apis/fusiontables/docs/samples/style/default.css" rel="stylesheet" type="text/css">

    <script type="text/javascript" src="http://www.google.com/jsapi"></script>

    <script type="text/javascript"
        src="http://maps.google.com/maps/api/js?sensor=false"></script>


    <!-- SCRIPT TO GET THE FUSIONTABLE PARAMETERS FOR SELECTING -->
    <script>

      // ======================================
      // GLOBAL VARIABLES
      // ======================================

      var tableId = '110yPR0tglVbjAHiXL_DNOcXriZGgdWG7lpyPiZM';
      


      // ======================================
      // LOAD REQUIRED LIBRARIES
      // ======================================
      
      //First load the visualization lib
      google.load('visualization', '1');

      // Unclear why need to include this package,
      // when lib is already loaded
      google.load('visualization', '1', { packages: ['table'] });



      // ======================================
      // FUNCTIONS
      // ======================================

      // ======================================
      // FUNCTION TO SHOW AND HIDE TOOL TIPS
      // ======================================

      function showBox(text, obj) {
        helpNode = document.createElement('div');
        helpNode.id = 'popBox';
        helpNode.setAttribute('class','popBox');
        helpNode.innerHTML = text;
        obj.appendChild(helpNode);
      }

      function hideBox() {
        node = document.getElementById('popBox');
        node.parentNode.removeChild(node);
      }
      
      // ======================================
      // FUNCTION TO UPDATE TABLE BY FIELD SELECTION
      // ======================================
      function formList(fieldLabel,selectId) {
        
        // Form the query for the location name
        var query = "SELECT \'" + fieldLabel + "\' FROM " + tableId;

        // Make a url query, for google viz API
        query = encodeURIComponent(query);
        var gvizQuery = new google.visualization.Query(
            'http://www.google.com/fusiontables/gvizdata?tq=' + query);

        // Send query and handle response
        gvizQuery.send(function(response) {
          var numRows = response.getDataTable().getNumberOfRows();

          // For each row in the table, add location name to list
          // as long as the name is not already there
          var locArray = new Array();
          for (var i = 0; i < numRows; i++) {

            var loc = response.getDataTable().getValue(i, 0);

            var is_duplicate = false;
            for (var j = 0; j < locArray.length; j++) {
              if ( loc == locArray[j] )
              {
                is_duplicate = true;
              }
            }

            if ( !is_duplicate )
            {
              locArray.push(loc);
            }
            locArray.sort();
          }

          // DEBUGN
          /*
          // List the unique locations
          document.getElementById("debug").innerHTML =
            locArray.join(",<br />");
          */

          // Populate the select box with unique locations
          var select = document.getElementById(selectId);
          for (var i = 0; i < locArray.length; i++ )
          {
            var l = select.options.length;
            var t = locArray[i];
            select.options[l] = new Option(t,t);
          }

        });

      }

      // Populate region button options
      var regionFieldLabel = 'Country or Region';
      var regionButtonId = 'locSelect';
      google.setOnLoadCallback(function () {
        formList(regionFieldLabel,regionButtonId);
      });

      // Populate Sector button options
      var sectorFieldLabel = 'Sector';
      var sectorButtonId = 'sectorSelect';
      google.setOnLoadCallback(function () {
        formList(sectorFieldLabel,sectorButtonId);
      });



      // =============================================
      // FUNCTION TO FORM WHERE QUERY
      // =============================================
      function genWhereQuery( locSelectId, sectorSelectId, sizeSelectId ) {

        // Get each dropdown value    
        var loc = document.getElementById(locSelectId).value;
        var sector = document.getElementById(sectorSelectId).value;
        var size = document.getElementById(sizeSelectId).value;

        // Construct the specific query pieces
        var locPhrase = " 'Country or Region' = '" + loc + "'";
        var sectorPhrase = " 'Sector' = '" + sector + "'";
        var sizePhrase = " 'Project Size' " + size;

        // Form a query matrix to loop over
        var ary = [
          [loc, locPhrase],
          [sector, sectorPhrase],
          [size, sizePhrase] ];

        // Loop over query pieces to build out full query
        var first = true;
        var query = '';
        for (var i=0; i<ary.length; i++ )
        {
          //alert('ary[' + i + ']['0
          if ( ary[i][0] )
          {
            var qual = ' AND';
            if (first)
            {
              // First needs a where, rest are anded
              qual = ' WHERE';
              first = false;
            }
            query += qual + ary[i][1];
          }
        }
        return query;
      }



      // =============================================
      // FUNCTION TO DRAW THE TABLE
      // =============================================
      function drawTable( locSelectId, sectorSelectId, sizeSelectId ) {
        
        // Contruct base of query
        var query = "SELECT 'Country or Region' as Location, " +
            "'Project Title' as Title " +
            'FROM ' + tableId;
        
        // Append the query with where clause    
        var whereClause = genWhereQuery( locSelectId,
                                sectorSelectId,
                                sizeSelectId );
        query += whereClause;
        
        // DEBUGN
        // alert('tab where = ' + whereClause);

        // This just replaces spaces with %20
        var queryText = encodeURIComponent(query);

        // Build out full query URL
        var gvizQuery = new google.visualization.Query(
            'http://www.google.com/fusiontables/gvizdata?tq=' + queryText);

        // Send that shit !
        gvizQuery.send(function(response) {
          var table = new google.visualization.Table(
              document.getElementById('project_table'));
          table.draw(response.getDataTable(), {
            showRowNumber: false,
            sortColumn: 0,
            sortAscending: true,
          });
        });
      }
      
      // Set table to be redrawn when called back (box selection)
      google.setOnLoadCallback(function () {
        drawTable(regionButtonId,sectorButtonId,'sizeSelect');
      });
    


      // =============================================
      // FUNCTION TO ADD THE MAP AND LEGEND
      // =============================================
      function initialize() {

        var locationColumn = 'Mapped Location';
        var mapElement = document.getElementById('map-canvas');
        var map = new google.maps.Map(mapElement,
          {
            center: new google.maps.LatLng(6, -15),
            zoom: 2,
            mapTypeId: google.maps.MapTypeId.ROADMAP,
            zoomControlOptions: {
              style: google.maps.ZoomControlStyle.SMALL
            },
          });

        var layer = new google.maps.FusionTablesLayer(
          {
            query:
            {
              select: locationColumn, 
              from: tableId
            },
            map: map,
            styleId: 2,
            templateId: 2,
          });



        // Create the legend and display on the map
        var legend = document.createElement('div');
        legend.id = 'legend';
        var content = [];
        content.push('<h3>Project Source</h3>');
        content.push('<p><div class="color red"></div>World Bank</p>');
        content.push('<p><div class="color yellow"></div>AfDB</p>');
        content.push('<p><div class="color green"></div>IDB</p>');
        content.push('<p><div class="color blue"></div>Wash DC</p>');
        content.push('<p><div class="color purple"></div>ADB</p>');
        content.push('<p><div class="color white"></div>EBRD</p>');
        content.push('<p><div class="color orange"></div>Post</p>');
        legend.innerHTML = content.join('');
        legend.index = 1;
        map.controls[google.maps.ControlPosition.LEFT_BOTTOM].push(legend);
      
        
        // Add listeners to all dropdowns to update the map
        var locSelector = document.getElementById('locSelect');
        var sectorSelector = document.getElementById('sectorSelect');
        var sizeSelector = document.getElementById('sizeSelect');

        google.maps.event.addDomListener(
          locSelector,
          'change',
          function() {updateMap(layer, tableId, locationColumn)} );
      
        google.maps.event.addDomListener(
          sectorSelector,
          'change',
          function() {updateMap(layer, tableId, locationColumn)} );

        google.maps.event.addDomListener(
          sizeSelector,
          'change',
          function() {updateMap(layer, tableId, locationColumn)} );
      }


      google.maps.event.addDomListener(window, 'load', initialize);

      
      // =============================================
      // FUNCTION TO UPDATE QUERY TO FT (MAP) BY SELECT
      // =============================================
      function updateMap(layer, tableId, locationColumn) {

        var locSelectId = 'locSelect';
        var sectorSelectId = 'sectorSelect';
        var sizeSelectId = 'sizeSelect';

        // Append the query with where clause    
        var whereClause = genWhereQuery( locSelectId,
                                         sectorSelectId,
                                         sizeSelectId );                    

        // Remove the WHERE tag                
        var where = whereClause.replace(' WHERE ','');
        
        // Get each dropdown value    
        var loc = document.getElementById(locSelectId).value;
        var sector = document.getElementById(sectorSelectId).value;
        var size = document.getElementById(sizeSelectId).value;

        if (loc || sector || size) {
          layer.setOptions({
            query: {
              select: locationColumn,
              from: tableId,
              where: where
            }
          });
        } else {
          layer.setOptions({
            query: {
              select: locationColumn,
              from: tableId
            }
          });
        }
      }

    </script>


    <style type="text/css">
        
      #map-canvas {
        height: 500px;
        width: 600px;
      }

      button {
        height: 40px;
        width: 200px;
        float:right;
      }

      #project_table {
        height: 500px;
        width: 500px;
         table-layout:fixed;
        overflow:auto;
      }

      #legend {
        background: #FFF;
        padding: 5px;
        margin: 5px;
        font-size: 10px;
        font-family: Arial, sans-serif;
      }

      #legend p {
        font-weight: bold;
      }

      #legend div {
        padding-bottom: 0px;
      }

      span {
        color: black;
        font-family: Arial, sans-serif;
        font-size: 12px;
      }

      p {
        color: black;
        font-family: Arial, sans-serif;
        font-size: 12px;
      }

      label {
        color: black;
        font-family: Arial, sans-serif;
        font-size: 12px;
      }

      h1 {
        color: black;
        font-family: Arial, sans-serif;
        font-size: 30px;
      }

      .color {
        height: 8px;
        width: 8px;
        margin-right: 3px;
        float: left;
        display: block;
        border: 1px solid;
      }
      
      .red {
        background: #C00;
      }

      .yellow {
        background: #FF3;
      }

      .green {
        background: #6F0;
      }

      .blue {
        background: #06C;
      }

      .purple {
        background: #E238EC;
      }
      .white {
        background: #FFF;
      }
      .orange {
        background: #9AFEFF;
      }

      .popBox {
        position: absolute;
        z-index: 2;
        background: #FFA500;
        padding: 0.3em;
        border: 1px solid gray;
      }

    </style>


  </head>
  <body>

    
    <button id="button:form" onclick="location.href='https://docs.google.com/spreadsheet/embeddedform?formkey=dGlYaVczaHl4SlVnSkhSN0FFMnRISmc6MQ';
    javascript:alert('By clicking okay you agree that any trade lead information added to this system will be unclassified only, and that you understand rules and regulations governing this site.')" style="background-color:orange">
    <a href="https://docs.google.com/spreadsheet/embeddedform?formkey=dGlYaVczaHl4SlVnSkhSN0FFMnRISmc6MQ">Add a Trade Lead</a>
    </button>

    <h1><u>Business Information Database System (BIDS)</u></h1>
    <p>

The Business Information Database System (BIDS) web site provides a means for users to more efficiently provide information to U.S. companies on potential business leads.  The interactive web site features a mapping function and database, which includes multilateral development bank projects and enables Posts to input business leads on a standardized form.  Users can 

<span onmouseover="showBox('Click on a project on the map for more information and to send a comment for inclusion on the site.', this)" onmouseout="hideBox()"><u>add unclassified comments and information</u></span> 

 that will assist U.S. companies to capitalize on international business opportunities and 

<span onmouseover="showBox('To input a new lead follow the link at the top right of this page.', this)" onmouseout="hideBox()"><u>add trade leads</u></span> 

they collect from their contacts.  We are working with the Department of Commerce to eventually deliver the leads you provide to U.S. businesses through their Export.gov website. 


    <br><br>
    </p>
    
    <div id="debug"></div>

    <label>Project Location:</label>
    <select 
      id="locSelect" 
      onchange="drawTable('locSelect','sectorSelect','sizeSelect');">
      <option value="">All</option>
    </select>

    <label>Sector:</label>
    <select 
      id="sectorSelect" 
      onchange="drawTable('locSelect','sectorSelect','sizeSelect');">
      <option value="">All</option>
    </select>
    
    <label>Size:</label>
    <select 
      id="sizeSelect"
      onchange="drawTable('locSelect','sectorSelect','sizeSelect');">
      <option value="">All</option>
      <option value="< 100000000">&lt;$100M</option>
      <option value="> 100000000">&gt;$100M</option>
    </select>
    
    <br>

    <div id="map_and_form" style="width:100%;">
      <div id="map-canvas" style="float:left;"></div>
      <div id="project_table" style="float:left; margin-left: 10px;"></div>
    </div>

    <br>


  </body>
</html>
